/**
 * Book数据模型模块
 */

// 导入SQLite3模型
const sqlite3 = require("sqlite3").verbose();
// 导入工具模块
const util = require("../../common/util");
// 导入配置模块
const config = require("../../common/config");

/**
 * BookDB构造函数
 * @constructor
 */
function BookDB() {
    this.dbFile = config.dbFile;
    this.instance = null;// Book数据库实例
    this.db = null; // SQLite3实例
}

/**
 * 创建BookDB对象
 */
BookDB.getInstance = function () {
    if (!this.instance) {
        this.instance = new BookDB();
    }
    return this.instance;
}

/**
 * 连接数据库
 * @returns {Promise}
 */
BookDB.prototype.connect = function () {
    return new Promise((resolve, reject) => {
        this.db = new sqlite3.Database(this.dbFile, err => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve("connect ok.");
            }
        });
    });
};

/**
 * 关闭数据库
 * @returns {Promise}
 */
BookDB.prototype.close = function () {
    return new Promise((resolve, reject) => {
        this.db.close(err => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve("close ok.");
            }
        });
    });
};

/**
 * 获取指定ID的书籍信息
 * @param {Number} bookId 书籍ID
 * @returns {Promise}
 */
BookDB.prototype.find = function (bookId) {
    return new Promise((resolve, reject) => {
        let sql = "SELECT * FROM books WHERE id = ?";
        let params = [bookId];
        this.db.get(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
};

/**
 * 获取书籍信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns {Promise}
 */
BookDB.prototype.findAll = function (limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
        let sql = "";
        let params = [];
        if (limit === -1) {
            sql="SELECT * FROM books ORDER BY id"
        } else if (offset === -1) {
            sql = "SELECT * FROM books ORDER BY id LIMIT ?";
            params[0] = limit;
        } else {
            sql = "SELECT * FROM books ORDER BY id LIMIT ? OFFSET ?";
            params[0] = limit;
            params[1] = offset;
        }
        this.db.all(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
};

/**
 * 新增书籍
 * @param {Object} book 书籍数据 
 * @returns {Promise}
 */
BookDB.prototype.add = function (book) {
    return new Promise((resolve, reject) => {
        let sql = `INSERT INTO books (
            title, pic, local_pic, author, publisher, producer,
            subtitle, originalTitle, translator, pubdate, pages, price,
            binding, series, isbn, intro, doubanId, created_time,
            updated_time
        ) VALUES (
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?
        );`;
        let params = [
            book.title, book.pic, book.localPic,
            book.author, book.publisher, book.producer,
            book.subtitle, book.originalTitle, book.translator,
            book.pubdate, book.pages, book.price,
            book.binding, book.series, book.isbn,
            book.intro, book.doubanId, book.createdTime,
            book.updatedTime];
        
        this.db.run(sql, params, function(err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.lastID);// 插入的数据的自增ID
            }
        });
    });
}

/**
 * 修改书籍
 * @param {Object} book 书籍数据 
 * @returns {Promise}
 */
BookDB.prototype.update = function (book) {
    return new Promise((resolve, reject) => {
        let sql = `UPDATE books SET
            title = ?, pic = ?, local_pic = ?, author = ?,
            publisher = ?, producer = ?,subtitle = ?, originalTitle = ?,
            translator = ?, pubdate = ?, pages = ?, price = ?,
            binding = ?, series = ?, isbn = ?, intro = ?,
            doubanId = ?, updated_time = ?
            WHERE id = ?
        ;`;
        let params = [
            book.title, book.pic, book.localPic,
            book.author, book.publisher, book.producer,
            book.subtitle, book.originalTitle, book.translator,
            book.pubdate, book.pages, book.price,
            book.binding, book.series, book.isbn,
            book.intro, book.doubanId, book.updatedTime,
            book.id];
        
        this.db.run(sql, params, function(err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes);// 影响的记录数
            }
        });
    });
};

/**
 * 删除书籍
 * @param {Number} bookId 书籍ID
 * @returns {Promise}
 */
BookDB.prototype.remove = function (bookId) {
    return new Promise((resolve, reject) => {
        let sql = "DELETE FROM books WHERE id = ?";
        let params = [bookId];
        this.db.run(sql, params, function(err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes);// 影响的记录数
            }
        });
    });
};

/**
 * 获取书籍总数
 * @returns {Promise}
 */
BookDB.prototype.getCount = function () {
    return new Promise((resolve, reject) => {
        let sql = "SELECT count(1) AS total FROM books";
        let params = [];
        this.db.get(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);// 书籍总数 
            }
        });
    });
};

/**
 * 获取指定关键字的书籍信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns {Promise}
 */
BookDB.prototype.search = function (q, limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
        let sql = "";
        q = `%${q}%`;
        let params = [q, q];
        if (limit === -1) {
            sql="SELECT * FROM books WHERE title LIKE ? OR author LIKE ?"
        } else if (offset === -1) {
            sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? LIMIT ?";
            params.push(limit);
        } else {
            sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? LIMIT ? OFFSET ?";
            params.push(limit);
            params.push(offset);
        }
        this.db.all(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
};

/**
 * 获取指定ISBN的书籍信息
 * @param {String} isbn 书籍ISBN
 * @returns {Promise}
 */
BookDB.prototype.findByIsbn = function (bookIsbn) {
    return new Promise((resolve, reject) => {
        let sql = "SELECT * FROM books WHERE isbn = ?";
        let params = [bookIsbn];
        this.db.get(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
};

module.exports = BookDB;